require 'mysql'
require 'http'
require 'date'
require 'time'


class DbAccess
  
  attr_accessor :hostname, :username, :password, :dbname, :mycon
  
  def initialize(hostname, username, password, dbname)
    @hostname = hostname
    @username = username
    @password = password
    @dbname = dbname
    @mycon = nil
  end
  
  def connect
    Mysql::new(@hostname, @username, @password)    
  end
  
  
  def createnew()
    
    @mycon = connect()
    sql = "DROP DATABASE IF EXISTS #{@dbname};"
    @mycon.query(sql)
    sql = "CREATE DATABASE #{@dbname};"
    @mycon.query(sql)
    @mycon.select_db(@dbname)
    createquotes()
    createstandardquote()
    createextendedquote()
    createhistoricalquote()
    createsymbols()
    createperf()
    createcorr()
    @mycon.close()
    
  end
  
  def createquotes
    
    sql = "CREATE TABLE quotes (symbol VARCHAR(40), name VARCHAR(40), last_price FLOAT, last_trade_date DATE, 
          last_trade_time TIME, price_change FLOAT, percent_change FLOAT, volume FLOAT, avg_daily_volume FLOAT, 
          bid FLOAT, ask FLOAT, previous_close FLOAT, todays_open FLOAT, days_range_lo FLOAT, days_range_hi FLOAT,
          yr_range_lo FLOAT, yr_range_hi FLOAT, eps FLOAT, pe_ratio FLOAT, dividend_pay_date VARCHAR(40), 
          dividend_per_share FLOAT, dividend_yield FLOAT, market_cap FLOAT, stock_exchange VARCHAR(40), 
          short_ratio FLOAT, target_price FLOAT, eps_est_this_year FLOAT, eps_est_next_year FLOAT, 
          eps_est_next_quarter FLOAT, price_over_eps_this_year FLOAT, price_over_eps_next_year FLOAT, 
          peg_ratio FLOAT, book_value FLOAT, price_over_book FLOAT, price_over_sales FLOAT, ebitda FLOAT,
          fifty_day_average FLOAT, two_hundred_day_average FLOAT, needs_adjust INT, PRIMARY KEY(symbol, last_trade_date, last_trade_time));"
    @mycon.query(sql)
    
  end
  
  def createextendedquote
    
    sql = "CREATE TABLE extendedquote (pricePaid FLOAT, pricePerSales FLOAT, pricePerBook FLOAT, commision FLOAT,
      movingAve200daysChangePercentFrom FLOAT, movingAve200daysChangeFrom FLOAT, movingAve200days FLOAT,
      movingAve50daysChangePercentFrom FLOAT, movingAve50daysChangeFrom FLOAT, movingAve50days FLOAT,
      ebitda FLOAT, marketCap FLOAT, weeks52ChangePercentFromLow FLOAT, weeks52ChangeFromLow FLOAT,
      pegRatio FLOAT, pricePerEPSEstimateNextYear FLOAT, pricePerEPSEstimateCurrentYear FLOAT, 
      dividendPayDate FLOAT, epsEstimateNextQuarter FLOAT, epsEstimateNextYear FLOAT, epsEstimateCurrentYear FLOAT,
      stockExchange VARCHAR(40), lowLimit FLOAT, highLimit FLOAT, dayChangeValue FLOAT, bookValue FLOAT,
      dividendYield FLOAT, dividendPerShare FLOAT, exDividendDate FLOAT, peRatio FLOAT, earningsPerShare FLOAT,
      weeks52Low FLOAT, weeks52High FLOAT, name VARCHAR(40), symbol VARCHAR(40), oneYearTargetPrice FLOAT,
      annualizedGain FLOAT, holdingsGain FLOAT, notes FLOAT, tradeDate DATE, holdingsValue FLOAT,
      weeks52ChangePercentFromHigh FLOAT, weeks52ChangeFromHigh FLOAT, sharesOwned FLOAT, shortRatio FLOAT,
      PRIMARY KEY(symbol, tradeDate));"
    
      @mycon.query(sql)
       
    
  end
  
  def createstandardquote
    
    sql = "CREATE TABLE standardquote (ask FLOAT, bid FLOAT, volume FLOAT, dayLow FLOAT, dayHigh FLOAT, 
              open FLOAT, previousClose FLOAT, changePercent FLOAT, name VARCHAR(40), symbol VARCHAR(40), 
              changePoints FLOAT, lastTrade FLOAT, tradeTime TIME, tradeDate DATE, averageDailyVolume FLOAT, 
              PRIMARY KEY(symbol, tradeDate, tradeTime));"
      
    @mycon.query(sql)
      
  end
  
  def createhistoricalquote
        
      sql = "CREATE TABLE historicalquote (symbol VARCHAR(40), tradeDate DATE, open FLOAT, high FLOAT, low FLOAT, close FLOAT,
          volume FLOAT, adjClose FLOAT,
          PRIMARY KEY(symbol, tradeDate));"
      @mycon.query(sql)
        
    end
  
  def createrealtimequote
        
      sql = "CREATE TABLE realtimequote (name VARCHAR(40), symbol VARCHAR(40), bid FLOAT, ask FLOAT, 
        marketCap FLOAT, dayLow FLOAT, dayHigh FLOAT, changePoints FLOAT, tradeTime TIME, tradeDate DATE, 
        lowChange FLOAT, hiChange FLOAT, 
        PRIMARY KEY(symbol, tradeDate, tradeTime));"
      @mycon.query(sql)
        
    end
  
  def createsymbols
    
    sql = "CREATE TABLE symbols (symbol VARCHAR(40), company_name VARCHAR(40), 
         company_description VARCHAR(2048), exchange VARCHAR(40), PRIMARY KEY(symbol, exchange));"
     @mycon.query(sql)
     
  end
  
  def createperf
    
    sql = "CREATE TABLE perf (symbol VARCHAR(40), last_trade_date DATE, value FLOAT, timed FLOAT, 
          PRIMARY KEY(symbol, last_trade_date, timed));"
    @mycon.query(sql)
    
  end
  
  def createcorr
    
    sql = "CREATE TABLE correlations (symbolA VARCHAR(40), symbolB VARCHAR(40), value FLOAT, PRIMARY KEY(symbolA, symbolB));"
    @mycon.query(sql)
    
  end
    
  def loadsymbols(file, exchange)
    
    @mycon = connect()
    @mycon.select_db(@dbname)
    
    infofile = File.open(file)
    infofile.each_line do |line|
      line = line.strip
      if (line.match("Symbol") != nil)
        next
      end
      subs = line.split("\t")
      puts "Loading record #{subs[0]} : #{subs[1]}"
      sql = "REPLACE INTO `symbols` (`symbol`, `company_name`, `company_description`, `exchange`) VALUES (\"#{subs[0]}\", \"#{subs[1]}\", NULL, \"#{exchange}\");"
      @mycon.query(sql)
      
    end 
    infofile.close()
    @mycon.close()
    
  end
  
  def loadcorrelationdata(inputdir)
      
    if (!File.exists?(inputdir)) then
      raise "Error: " + inputdir + " does not exist."
    end
    
    @mycon = connect()
    @mycon.select_db(@dbname)
    
    datafiles = Dir["#{inputdir}/*.txt"]
    datafiles.each do |thefile|
      
      subs = File.basename(thefile).split(".")
      symbolA = subs[0]
      
      infofile = File.open(thefile)
      infofile.each_line do |line|
        line = line.strip
        subs = line.split(",")
        symbolB = subs[0]
        value = subs[1].to_f
        puts "Loading record #{symbolA} : #{symbolB} : #{value}"
        sql = "REPLACE INTO `correlations` (`symbolA`, `symbolB`, `value`) VALUES (\"#{symbolA}\", \"#{symbolB}\", #{value});"
        @mycon.query(sql)
        
      end 
      infofile.close()
      
      
    end
    
    @mycon.close()
      
  end
  
  
end

